
# https://www.cnblogs.com/daisy89/p/5307727.html

import cx_Oracle

from job.src.function.record_log import mylog


class OprationOracle():
    def __init__(self, **kwargs):
        self.username = kwargs['username']
        self.password = kwargs['password']
        if 'othername' in kwargs:
            self.othername = kwargs['othername']
        if 'host' in kwargs:
            self.host = kwargs['host']
        if 'zeroname' in kwargs:
            self.zeroname = kwargs['zeroname']
        if 'port' in kwargs:
            self.listen_port = kwargs['port']
        else:
            self.listen_port = 1521

    def connect(self, flag=0):
        '''
        连接oracle数据库
        :param flag: 1:使用别名连接，别名见数据库安装的tnsnames.ora
                     2:使用 数据库主机ip + server_name登陆
                     3.配置监听端口
        :return:
        '''
        if flag == 0:
            address = self.username + '/' + self.password + '@' + self.othername
            mylog.info(f'Link addres: {address}')
            self.db = cx_Oracle.connect(address)
        elif flag == 1:
            address = self.username + '/' + self.password + '@' + self.host + '/' + self.zeroname
            mylog.info(f'Link addres: {address}')
            self.db = cx_Oracle.connect(address)
        elif flag == 2:
            tns = cx_Oracle.makedsn(self.host, self.listen_port, self.zeroname)
            mylog.info(f'Link addres: {tns}')
            self.db = cx_Oracle.connect(self.username, self.password, tns)
        elif flag == 3:
            address = self.username + '/' + self.password + '@' + self.othername
            mylog.info(f'Link addres: {address}')
            self.db = cx_Oracle.connect(address, mode=cx_Oracle.SYSDBA)
        else:
            log_warn('连接模式失败！')
        # print('Oracle client connect successful.')
        mylog.info('Oracle client connect successful.')
        return self.db

    def close(self):
        self.db.close()

    def sql_select(self, sql, flag=True):
        cr = self.db.cursor()
        mylog.info(f'Act sql: {sql}')
        cr.execute(sql)
        if flag:
            res = cr.fetchall()
            mylog.info(f'Result: {str(res)}')
        else:
            res = []
        return res

    def sql_DML(self, sql, params=None):
        cr = self.db.cursor()
        if params is None:
            cr.execute(sql)
        else:
            cr.execute(sql, params)
        cr.close()
        self.db.commit()
        return True



